package com.ceuer.pet.dao.impl;

import com.ceuer.pet.dao.PetDao;
import com.ceuer.pet.entity.Breed;
import com.ceuer.pet.entity.Pet;
import com.ceuer.pet.entity.Species;
import com.ceuer.pet.util.DateUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class PetDaoImpl extends BaseDao implements PetDao {
	
	@Override
	public List<Pet> selectIndexAll() {
		List<Pet> list= new ArrayList<>();
		Connection connection=null;
		PreparedStatement preparedStatement=null;
		ResultSet resultSet=null;
		try {
			String sql="select p.id,p.nickname,p.age,p.sex,p.remrk,p.owner,s.id speciesid,s.name speciesname,b.id as breedid,b.name breedname ";
			sql+="from pet p,breed b,species s ";
			sql+="where p.breedid=b.id and b.speciesid=s.id and p.owner is null";
			connection=this.getConn();
			preparedStatement=connection.prepareStatement(sql);
			resultSet=preparedStatement.executeQuery();
			while (resultSet.next()){
				Pet pet = new Pet();
				this.setPet(resultSet, pet);
				list.add(pet);
			}
		}catch (Exception e){
			e.printStackTrace();
		}finally {
			this.closeAll(connection, preparedStatement, resultSet);
		}
		return list;
	}
	
	public List<Pet> selectOwnerAll(Integer ownerId) {
		List<Pet> list= new ArrayList<>();
		Connection connection=null;
		PreparedStatement preparedStatement=null;
		ResultSet resultSet=null;
		try {
			String sql="select p.id,p.nickname,p.age,p.sex,p.remrk,p.owner,s.id speciesid,s.name speciesname,b.id as breedid,b.name breedname ";
			sql+="from pet p,breed b,species s ";
			sql+="where p.breedid=b.id and b.speciesid=s.id and p.owner=?";
			connection=this.getConn();
			preparedStatement=connection.prepareStatement(sql);
			preparedStatement.setInt(1,ownerId);
			resultSet=preparedStatement.executeQuery();
			while (resultSet.next()){
				Pet pet = new Pet();
				this.setPet(resultSet, pet);
				list.add(pet);
			}
		}catch (Exception e){
			e.printStackTrace();
		}finally {
			this.closeAll(connection, preparedStatement, resultSet);
		}
		return list;
	}
	
	@Override
	public Pet selectOne(Integer id) {
		Pet pet=null;
		Connection connection=null;
		PreparedStatement preparedStatement=null;
		ResultSet resultSet=null;
		try {
			String sql="select p.id,p.nickname,p.age,p.sex,p.remrk,s.id speciesid,s.name speciesname,b.id as breedid,b.name breedname ";
			sql+="from pet p,breed b,species s ";
			sql+="where p.breedid=b.id and b.speciesid=s.id ";
			sql+="and p.id=?";
			connection=this.getConn();
			preparedStatement=connection.prepareStatement(sql);
			preparedStatement.setInt(1, id);
			resultSet=preparedStatement.executeQuery();
			while (resultSet.next()){
				pet = new Pet();
				this.setPet(resultSet, pet);
			}
		}catch (Exception e){
			e.printStackTrace();
		}finally {
			this.closeAll(connection, preparedStatement, resultSet);
		}
		return pet;
	}
	
	public boolean selectPetByOwner(Integer petId, Integer ownerId) {
		boolean re=false;
		Connection connection=null;
		PreparedStatement preparedStatement=null;
		ResultSet resultSet=null;
		try {
			String sql="select count(*) count from pet where id=? and owner=?";
			connection=this.getConn();
			preparedStatement=connection.prepareStatement(sql);
			preparedStatement.setInt(1, petId);
			preparedStatement.setInt(2, ownerId);
			resultSet=preparedStatement.executeQuery();
			while (resultSet.next()){
				re=resultSet.getInt("count")==1 ? true : false;
			}
		}catch (Exception e){
			e.printStackTrace();
		}finally {
			this.closeAll(connection, preparedStatement, resultSet);
		}
		return re;
	}
	
	private void setPet(ResultSet resultSet, Pet pet){
		try {
			pet.setId(resultSet.getInt("id"));
			pet.setNickname(resultSet.getString("nickname"));
			pet.setAge(resultSet.getInt("age"));
			pet.setSex(resultSet.getInt("sex"));
			pet.setRemrk(resultSet.getString("remrk"));
			// pet.setCreatetime(resultSet.getString("createtime"));
			// pet.setCreateuser(resultSet.getInt("createuser"));
			pet.setOwner(resultSet.getInt("owner")==0 ? null : resultSet.getInt("owner"));
			// pet.setOwnertime(resultSet.getString("ownertime"));
			Species species = new Species();
			Breed breed = new Breed();
			species.setId(resultSet.getInt("speciesid"));
			species.setName(resultSet.getString("speciesname"));
			breed.setId(resultSet.getInt("breedid"));
			breed.setName(resultSet.getString("breedname"));
			breed.setSpecies(species);
			pet.setBreed(breed);
		}catch (Exception e){
			e.printStackTrace();
		}
	}
	
	@Override
	public boolean add(Pet pet) {
		String sql="insert into pet(breedid,nickname,age,sex,remrk,createtime,createuser) values (?,?,?,?,?,?,?)";
		List<Object> param=new ArrayList<>();
		param.add(pet.getBreed().getId());
		param.add(pet.getNickname());
		param.add(pet.getAge());
		param.add(pet.getSex());
		param.add(pet.getRemrk());
		param.add(DateUtil.now());
		param.add(pet.getCreateuser());
		int num=this.executeUpdate(sql,param);
		return num==1;
	}
	
	@Override
	public boolean update(Pet pet) {
		String sql="update pet set breedid=?,nickname=?,nickname=?,age=?,sex=?,remrk=?,createtime=?,createuser=? where id=?";
		List<Object> param=new ArrayList<>();
		param.add(pet.getBreed().getId());
		param.add(pet.getNickname());
		param.add(pet.getAge());
		param.add(pet.getSex());
		param.add(pet.getRemrk());
		param.add(DateUtil.now());
		param.add(pet.getCreateuser());
		param.add(pet.getId());
		int num=this.executeUpdate(sql,param);
		return num==1;
	}
	
	public boolean adopt(Integer petId,Integer ownerId) {
		String sql="update pet set owner=?,ownertime=? where id=?";
		List<Object> param=new ArrayList<>();
		param.add(ownerId);
		param.add(DateUtil.now());
		param.add(petId);
		int num=this.executeUpdate(sql,param);
		return num==1;
	}
}
